﻿CREATE TRIGGER [dbo].[TRG_PRODUCT_UNIT_UPDATE]
   ON  [dbo].[PRODUCT_UNIT]
   AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
	
	DECLARE @Pid int
	DECLARE @PU_ID int
	DECLARE @VatInvID int
	DECLARE @GuildBillNum int
	DECLARE @DirectNum int
	DECLARE @State int
	DECLARE @Cursor cursor
	
	SET @cursor = CURSOR SCROLL FOR
		SELECT ID, DIRECT_NUMBER, GUILD_BILL_NUMBER, VAT_INVOICE_ID, PID FROM inserted

	open @Cursor
	
		FETCH NEXT FROM @Cursor INTO @PU_ID, @DirectNum, @GuildBillNum, @VatInvID, @Pid
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @Pid IS NULL
		BEGIN
			UPDATE PRODUCT_UNIT
			SET LAST_UPDATE = GETDATE(), 
			LAST_UPDATE_USER = SUSER_NAME()
			WHERE ID = @PU_ID
		END
		ELSE
		BEGIN
			IF @VatInvID IS NOT NULL
			BEGIN
				SET @State = 4
			END
			ELSE IF @GuildBillNum IS NOT NULL
			BEGIN
				SET @State = 3
			END
			ELSE IF @DirectNum IS NOT NULL
			BEGIN
				SET @State = 2
			END
			ELSE
			BEGIN
				SET @State = 1
			END
	
			UPDATE PRODUCT_UNIT
			SET NSI_PRODUCT_STATE_ID = @State, LAST_UPDATE = GETDATE(), 
			LAST_UPDATE_USER = SUSER_NAME()
			WHERE ID = @PU_ID
		END
		
		FETCH NEXT FROM @Cursor INTO @PU_ID, @DirectNum, @GuildBillNum, @VatInvID, @Pid
	END
	
	close @Cursor
END
